package com.foreknow.demo18;

import java.sql.*;

/**
 * 对dept增删改查操作
 * 完成DML
 */
public class DeptDemo {
    //获取连接
    private Connection conn;
    //获取到预处理
    private  PreparedStatement pstmt;
    //查询结果
    private ResultSet rs;
    /**
     *创建链接
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public Connection getConnection() throws ClassNotFoundException, SQLException {
        //1.加载驱动程序
        //如果是8.0以上的版本需要使用com.mysql.cj.jdbc.Driver
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        //8.0以上: jdbc:mysql://localhost:3306/basic2?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
        //8.0以下版本：jdbc:mysql://localhost:3306/basic2
        conn = DriverManager. getConnection("jdbc:mysql://localhost:3306/basic2","root","123456");
        return conn;
    }

    /**
     ** 查询
     * @param sql SQL语句
     * @param values 动态可变参数  Object[]
     * @return
     * @throws SQLException
     */
    public ResultSet getQuery(String sql,Object...values) throws SQLException {
        //获取到预处理
        pstmt= conn.prepareStatement(sql);
        //遍历数组
        for (int i=0;i<values.length;i++){
            pstmt.setObject(i+1,values[i]);
        }
         rs = pstmt.executeQuery();
        //思考：对结果集是遍历好还是不遍历好呢？不需要遍历
        //如果遍历，以后要通过这个方法再查询别的表就用不了这个方法了
        return rs;
    }

    /**
     * DML操作
     * @param sql
     * @param values
     * @return
     * @throws SQLException
     */
    public int Dml(String sql,Object...values) throws SQLException {
        //获取到预处理
        pstmt= conn.prepareStatement(sql);
        //遍历数组
        for (int i=0;i<values.length;i++){
            pstmt.setObject(i+1,values[i]);
        }
        int aa = pstmt.executeUpdate();
        return aa;
    }

    /**
     * 释放资源
     */
    public void closed() throws SQLException {
        if(rs!=null) {
            rs.close();
        }
        if(pstmt!=null) {
            pstmt.close();
        }
        if(conn!=null) {
            conn.close();
        }
    }
    public static void main(String[] args) {
        DeptDemo deptDemo = new DeptDemo();

        try {
            //1. 连接数据库
            deptDemo.getConnection();
           // 2. 查询
           ResultSet rs= deptDemo.getQuery("select * from dept where deptno=?",10);
           while (rs.next()){
              int deptno= rs.getInt("deptno");
              String dname= rs.getString("dname");
              String loc = rs.getString("loc");
              System.out.println(deptno+"    "+dname+"   "+loc);
           }
           //释放资源
           rs.close();
           //3.删除
          int isDelete =  deptDemo.Dml("delete from dept where deptno=? ",1);
            System.out.println(isDelete);
            //释放资源
            rs.close();

            //增加
          int isRigth =  deptDemo.Dml("insert into dept(deptno,dname,loc) values(?,?,?)",1,"222","2222");
            System.out.println(isRigth);
            //释放资源
            rs.close();

//            //修改
            //修改的数据怎么写活了
            int isupdate =deptDemo.Dml("update dept set dname =? where deptno=?",10,10);
            System.out.println(isupdate);
            //释放资源
            rs.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
